package com.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.dao.StudentDAO;
import com.dto.StudentDTO;
import com.util.db.DBUtil;

public class StudentDAOImpl implements StudentDAO {
	private Connection conn;

	public StudentDAOImpl() {
		conn = DBUtil.getConnection();
	}

	@Override
	public StudentDTO findStudentByName(String username) {
		StudentDTO studentDTO = null;
		String sql = "SELECT * FROM student WHERE user_name=?;";
		QueryRunner qr = new QueryRunner();

		try {
			studentDTO = qr.query(conn, sql, new BeanHandler<StudentDTO>(
					StudentDTO.class), username);
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return studentDTO;
	}

	@Override
	public boolean findStudent(String username, String password) {
		boolean result = true;
		StudentDTO studentDTO = null;
		String sql = "SELECT * FROM student WHERE user_name=? AND password=?;";
		QueryRunner qr = new QueryRunner();

		try {
			studentDTO = qr.query(conn, sql, new BeanHandler<StudentDTO>(
					StudentDTO.class), username, password);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		result = (studentDTO == null) ? false : true;
		return result;
	}

	@Override
	public List<StudentDTO> getStudents() {
		List<StudentDTO> students = null;
		String sql = "SELECT * FROM student;";
		QueryRunner qr = new QueryRunner();

		try {
			students = qr.query(conn, sql, new BeanListHandler<StudentDTO>(
					StudentDTO.class));
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return students;
	}

	@Override
	public boolean saveStudent(StudentDTO studentDTO) {
		boolean result = true;
		int insertRows = 0;
		String sql = "INSERT INTO student VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		QueryRunner qr = new QueryRunner();
		Object[] params = new Object[10];
		params[0] = studentDTO.getS_id();
		params[1] = studentDTO.getUser_name();
		params[2] = studentDTO.getName();
		params[3] = studentDTO.getPassword();
		params[4] = studentDTO.getSex();
		params[5] = studentDTO.getAge();
		params[6] = studentDTO.getTelephone();
		params[7] = studentDTO.getEmail();
		params[8] = studentDTO.getDepart();
		params[9] = studentDTO.getMajor();

		try {
			insertRows = qr.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		result = (insertRows == 0) ? false : true;
		return result;
	}

}
